import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
PASSWORD = 'kongxingli2002'
DBNAME = 'supermarket'
search_keys = {"员工": "员工编号", "供应商": "供应商编号", "商品": "商品编号", "主表": "采购清单号"}  # 表：主码

class Connect:
    #连接数据库
    def __init__(self):  # self表示实例对象
        try:
            #连接数据库
            self.db = pymysql.connect(host=DBHOST, user=DBUSER, password=PASSWORD, database=DBNAME)
            print("数据库连接成功")
        except pymysql.Error as e:
            self.db = None
            print('数据库连接失败: ' + str(e))
    #获得属性组
    def get_columns(self, table):
        if not self.db:
            print("数据库连接失败，无法获取列")
            return []

        try:
            with self.db.cursor() as cur:
                sql = f'SHOW COLUMNS FROM {table}'
                cur.execute(sql)
                results = cur.fetchall()
                columns = [row[0] for row in results]
                print("1")
                print(columns)
                return columns
        except pymysql.Error as e:
            print('获取列失败: ' + str(e))
            return []

    #向表插入数据
    def insert(self, table, data):
        if not self.db:
            print("数据库连接失败，无法插入数据")
            return
        try:
            with self.db.cursor() as cur:
                columns = ','.join(data.keys())
                columns_values = ','.join(['%s'] * len(data))
                values = tuple(data.values())
                sql = f'INSERT INTO {table}({columns}) VALUES ({columns_values})'
                cur.execute(sql, values)
                self.db.commit()
                print("数据插入成功")
        except pymysql.Error as e:
            print('数据插入失败: ' + str(e))
            self.db.rollback()

    #查询数据库
    def select(self, table):
        if not self.db:
            print("数据库连接失败，无法查询数据")
            return []

        try:
            with self.db.cursor() as cur:
                sql = f'SELECT * FROM {table}'
                cur.execute(sql)
                results = cur.fetchall()
                if not results:
                    print("没有找到任何数据")
                return results
        except pymysql.Error as e:
            print('数据库查询失败: ' + str(e))
            return []

    #更新表中的数据
    def update(self, table, set_values, condition_value):  # condition为主码或者别的码,condition_value为主码的值，用于where查询
        condition=search_keys[table]
        if not self.db:
            print("数据库连接失败，无法更新数据")
            return
        try:
            with self.db.cursor() as cur:
                new_data = ','.join(f"{key}=%s"for key in set_values.keys())
                print(new_data)
                sql = f'UPDATE {table} SET {new_data} WHERE {condition}=%s'
                values = list(set_values.values())
                values.append(condition_value)
                cur.execute(sql, values)
                self.db.commit()
                print("数据库更新成功")
        except pymysql.Error as e:
            print('数据库更新失败: ' + str(e))
            self.db.rollback()

    #查询数据
    def search_data(self,table,search_value):
        search_key = search_keys[table]
        if not self.db:
            print("数据库连接失败，无法搜索数据")
            return
        try:
            with self.db.cursor() as cur:
                sql=f'SELECT * FROM {table} WHERE {search_key}=%s'
                cur.execute(sql, (search_value))
                result=cur.fetchall()
                if result:
                    print("数据存在")
                    return True,result
                else:
                    print("数据不存在")
                    return False,result
        except pymysql.Error as e:
            print('数据搜索失败: ' + str(e))
            self.db.rollback()

    #删除表中的数据
    def delete(self, table, search_value):
        search_key=search_keys[table]
        if not self.db:
            print("数据库连接失败，无法删除数据")
            return
        try:
            with self.db.cursor() as cur:
                sql = f'DELETE FROM {table} WHERE {search_key}=%s'
                cur.execute(sql, (search_value))
                self.db.commit()
                print("数据删除成功")
        except pymysql.Error as e:
            print('数据删除失败: ' + str(e))
            self.db.rollback()

    #关闭数据库
    def __del__(self):
        if self.db:
            self.db.close()
            print("数据库连接已关闭")

if __name__ == '__main__':
    db = Connect()
    db.get_columns('users')